﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using LibHuman;

namespace Human
{
	public class frmbc_Pcap_pttt: System.Windows.Forms.Form
	{
        AccessData mDB = new AccessData();
        Language lan = new Language();
        DataSet  dt_rp = new DataSet();
        DataSet dsHd = new DataSet();
        DataSet ds_loaipt = new DataSet();
        DataTable _dtam=new DataTable();
        DataTable dt_dmgia = new DataTable();
		private frmReport  rpt;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.TextBox txtnam;
		private System.Windows.Forms.Button buttonbcao;
        private System.Windows.Forms.Button buttonkethuc;	
		private string thang,nam,tn,msg="",user_dn="";
		private System.Windows.Forms.TextBox txtthang;
		private System.Windows.Forms.Label label5;
		private System.Windows.Forms.ToolTip toolTip1;
        private System.Windows.Forms.ComboBox combodvi;
        private ComboBox cboLoainv;
        private Label label3;
        private GroupBox groupKx;
        private RadioButton rdExcel;
        private RadioButton rdIn;
        private RadioButton optMH;
        private GroupBox groupBox2;
        private GroupBox gpBPTTT;
        private RadioButton rdTT;
        private RadioButton rdPT;
        private ComboBox cboLoaipt;
        private Label label4;
		private System.ComponentModel.IContainer components;

		public frmbc_Pcap_pttt(string s_userdn)
		{			
			InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            user_dn = s_userdn;
		}
		
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            this.components = new System.ComponentModel.Container();
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(frmbc_Pcap_pttt));
            this.label1 = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.txtnam = new System.Windows.Forms.TextBox();
            this.buttonkethuc = new System.Windows.Forms.Button();
            this.buttonbcao = new System.Windows.Forms.Button();
            this.txtthang = new System.Windows.Forms.TextBox();
            this.label5 = new System.Windows.Forms.Label();
            this.toolTip1 = new System.Windows.Forms.ToolTip(this.components);
            this.combodvi = new System.Windows.Forms.ComboBox();
            this.cboLoainv = new System.Windows.Forms.ComboBox();
            this.label3 = new System.Windows.Forms.Label();
            this.groupKx = new System.Windows.Forms.GroupBox();
            this.rdExcel = new System.Windows.Forms.RadioButton();
            this.rdIn = new System.Windows.Forms.RadioButton();
            this.optMH = new System.Windows.Forms.RadioButton();
            this.groupBox2 = new System.Windows.Forms.GroupBox();
            this.gpBPTTT = new System.Windows.Forms.GroupBox();
            this.rdTT = new System.Windows.Forms.RadioButton();
            this.rdPT = new System.Windows.Forms.RadioButton();
            this.cboLoaipt = new System.Windows.Forms.ComboBox();
            this.label4 = new System.Windows.Forms.Label();
            this.groupKx.SuspendLayout();
            this.gpBPTTT.SuspendLayout();
            this.SuspendLayout();
            // 
            // label1
            // 
            this.label1.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label1.ForeColor = System.Drawing.Color.Navy;
            this.label1.Location = new System.Drawing.Point(40, 15);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(54, 20);
            this.label1.TabIndex = 26;
            this.label1.Text = "Tháng :";
            this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // label2
            // 
            this.label2.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label2.ForeColor = System.Drawing.Color.Navy;
            this.label2.Location = new System.Drawing.Point(38, 40);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(56, 20);
            this.label2.TabIndex = 27;
            this.label2.Text = "Đơn vị :";
            this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // txtnam
            // 
            this.txtnam.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.txtnam.ForeColor = System.Drawing.Color.Navy;
            this.txtnam.Location = new System.Drawing.Point(198, 14);
            this.txtnam.MaxLength = 4;
            this.txtnam.Name = "txtnam";
            this.txtnam.Size = new System.Drawing.Size(59, 22);
            this.txtnam.TabIndex = 1;
            this.txtnam.TextAlign = System.Windows.Forms.HorizontalAlignment.Center;
            this.txtnam.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtnam_KeyDown);
            this.txtnam.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.txtthang_KeyPress);
            // 
            // buttonkethuc
            // 
            this.buttonkethuc.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.buttonkethuc.ForeColor = System.Drawing.Color.Navy;
            this.buttonkethuc.Image = global::Human.Properties.Resources.close_r;
            this.buttonkethuc.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.buttonkethuc.Location = new System.Drawing.Point(155, 187);
            this.buttonkethuc.Name = "buttonkethuc";
            this.buttonkethuc.Size = new System.Drawing.Size(76, 28);
            this.buttonkethuc.TabIndex = 7;
            this.buttonkethuc.Text = "    &Thoát";
            this.toolTip1.SetToolTip(this.buttonkethuc, "Kết thúc");
            this.buttonkethuc.Click += new System.EventHandler(this.buttonkethuc_Click_1);
            // 
            // buttonbcao
            // 
            this.buttonbcao.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.buttonbcao.ForeColor = System.Drawing.Color.Navy;
            this.buttonbcao.Image = global::Human.Properties.Resources.ok;
            this.buttonbcao.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.buttonbcao.Location = new System.Drawing.Point(74, 187);
            this.buttonbcao.Name = "buttonbcao";
            this.buttonbcao.Size = new System.Drawing.Size(76, 28);
            this.buttonbcao.TabIndex = 5;
            this.buttonbcao.Text = "   &Báo cáo";
            this.toolTip1.SetToolTip(this.buttonbcao, "Báo cáo");
            this.buttonbcao.Click += new System.EventHandler(this.xem_Click);
            // 
            // txtthang
            // 
            this.txtthang.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.txtthang.ForeColor = System.Drawing.Color.Navy;
            this.txtthang.Location = new System.Drawing.Point(95, 14);
            this.txtthang.MaxLength = 2;
            this.txtthang.Name = "txtthang";
            this.txtthang.Size = new System.Drawing.Size(56, 22);
            this.txtthang.TabIndex = 0;
            this.txtthang.TextAlign = System.Windows.Forms.HorizontalAlignment.Center;
            this.txtthang.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtthang_KeyDown);
            this.txtthang.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.txtthang_KeyPress);
            // 
            // label5
            // 
            this.label5.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label5.ForeColor = System.Drawing.Color.Navy;
            this.label5.Location = new System.Drawing.Point(154, 15);
            this.label5.Name = "label5";
            this.label5.Size = new System.Drawing.Size(43, 18);
            this.label5.TabIndex = 37;
            this.label5.Text = " Năm :";
            this.label5.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // combodvi
            // 
            this.combodvi.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
            this.combodvi.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.combodvi.ForeColor = System.Drawing.Color.Navy;
            this.combodvi.ItemHeight = 14;
            this.combodvi.Location = new System.Drawing.Point(95, 40);
            this.combodvi.Name = "combodvi";
            this.combodvi.Size = new System.Drawing.Size(161, 22);
            this.combodvi.TabIndex = 2;
            this.combodvi.KeyDown += new System.Windows.Forms.KeyEventHandler(this.combodvi_KeyDown);
            // 
            // cboLoainv
            // 
            this.cboLoainv.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.cboLoainv.ForeColor = System.Drawing.Color.Navy;
            this.cboLoainv.ItemHeight = 14;
            this.cboLoainv.Location = new System.Drawing.Point(95, 65);
            this.cboLoainv.Name = "cboLoainv";
            this.cboLoainv.Size = new System.Drawing.Size(161, 22);
            this.cboLoainv.TabIndex = 42;
            // 
            // label3
            // 
            this.label3.AutoSize = true;
            this.label3.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label3.ForeColor = System.Drawing.Color.Navy;
            this.label3.Location = new System.Drawing.Point(14, 69);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(80, 13);
            this.label3.TabIndex = 43;
            this.label3.Text = "Loại nhân viên:";
            this.label3.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // groupKx
            // 
            this.groupKx.Controls.Add(this.rdExcel);
            this.groupKx.Controls.Add(this.rdIn);
            this.groupKx.Controls.Add(this.optMH);
            this.groupKx.Location = new System.Drawing.Point(46, 145);
            this.groupKx.Name = "groupKx";
            this.groupKx.Size = new System.Drawing.Size(223, 35);
            this.groupKx.TabIndex = 41;
            this.groupKx.TabStop = false;
            this.groupKx.Tag = "0";
            // 
            // rdExcel
            // 
            this.rdExcel.AutoSize = true;
            this.rdExcel.Location = new System.Drawing.Point(163, 13);
            this.rdExcel.Name = "rdExcel";
            this.rdExcel.Size = new System.Drawing.Size(50, 17);
            this.rdExcel.TabIndex = 2;
            this.rdExcel.TabStop = true;
            this.rdExcel.Tag = "2";
            this.rdExcel.Text = "Excel";
            this.rdExcel.UseVisualStyleBackColor = true;
            this.rdExcel.CheckedChanged += new System.EventHandler(this.rdExcel_CheckedChanged);
            // 
            // rdIn
            // 
            this.rdIn.AutoSize = true;
            this.rdIn.Location = new System.Drawing.Point(92, 13);
            this.rdIn.Name = "rdIn";
            this.rdIn.Size = new System.Drawing.Size(56, 17);
            this.rdIn.TabIndex = 1;
            this.rdIn.TabStop = true;
            this.rdIn.Tag = "1";
            this.rdIn.Text = "Máy in";
            this.rdIn.UseVisualStyleBackColor = true;
            this.rdIn.CheckedChanged += new System.EventHandler(this.rdIn_CheckedChanged);
            // 
            // optMH
            // 
            this.optMH.AutoSize = true;
            this.optMH.Checked = true;
            this.optMH.Location = new System.Drawing.Point(9, 13);
            this.optMH.Name = "optMH";
            this.optMH.Size = new System.Drawing.Size(68, 17);
            this.optMH.TabIndex = 0;
            this.optMH.TabStop = true;
            this.optMH.Tag = "0";
            this.optMH.Text = "Màn hình";
            this.optMH.UseVisualStyleBackColor = true;
            this.optMH.CheckedChanged += new System.EventHandler(this.optMH_CheckedChanged);
            // 
            // groupBox2
            // 
            this.groupBox2.Location = new System.Drawing.Point(7, 0);
            this.groupBox2.Name = "groupBox2";
            this.groupBox2.Size = new System.Drawing.Size(278, 222);
            this.groupBox2.TabIndex = 44;
            this.groupBox2.TabStop = false;
            // 
            // gpBPTTT
            // 
            this.gpBPTTT.Controls.Add(this.rdTT);
            this.gpBPTTT.Controls.Add(this.rdPT);
            this.gpBPTTT.Location = new System.Drawing.Point(49, 84);
            this.gpBPTTT.Name = "gpBPTTT";
            this.gpBPTTT.Size = new System.Drawing.Size(207, 34);
            this.gpBPTTT.TabIndex = 36;
            this.gpBPTTT.TabStop = false;
            this.gpBPTTT.Tag = "P";
            this.gpBPTTT.Enter += new System.EventHandler(this.gpBPTTT_Enter);
            // 
            // rdTT
            // 
            this.rdTT.AutoSize = true;
            this.rdTT.Location = new System.Drawing.Point(120, 12);
            this.rdTT.Name = "rdTT";
            this.rdTT.Size = new System.Drawing.Size(72, 17);
            this.rdTT.TabIndex = 1;
            this.rdTT.Text = "Thủ thuật";
            this.rdTT.UseVisualStyleBackColor = true;
            this.rdTT.CheckedChanged += new System.EventHandler(this.rdTT_CheckedChanged);
            // 
            // rdPT
            // 
            this.rdPT.AutoSize = true;
            this.rdPT.Checked = true;
            this.rdPT.Location = new System.Drawing.Point(26, 12);
            this.rdPT.Name = "rdPT";
            this.rdPT.Size = new System.Drawing.Size(78, 17);
            this.rdPT.TabIndex = 0;
            this.rdPT.TabStop = true;
            this.rdPT.Text = "Phẩu thuật";
            this.rdPT.UseVisualStyleBackColor = true;
            this.rdPT.CheckedChanged += new System.EventHandler(this.rdPT_CheckedChanged);
            // 
            // cboLoaipt
            // 
            this.cboLoaipt.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
            this.cboLoaipt.ForeColor = System.Drawing.Color.Navy;
            this.cboLoaipt.Location = new System.Drawing.Point(95, 123);
            this.cboLoaipt.Name = "cboLoaipt";
            this.cboLoaipt.Size = new System.Drawing.Size(161, 21);
            this.cboLoaipt.TabIndex = 38;
            this.cboLoaipt.Visible = false;
            this.cboLoaipt.SelectedIndexChanged += new System.EventHandler(this.cboLoaipt_SelectedIndexChanged);
            // 
            // label4
            // 
            this.label4.AutoSize = true;
            this.label4.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(0)))), ((int)(((byte)(0)))), ((int)(((byte)(160)))));
            this.label4.Location = new System.Drawing.Point(64, 127);
            this.label4.Name = "label4";
            this.label4.Size = new System.Drawing.Size(30, 13);
            this.label4.TabIndex = 37;
            this.label4.Text = "Loại:";
            this.label4.Visible = false;
            this.label4.Click += new System.EventHandler(this.label4_Click);
            // 
            // frmbc_Pcap_pttt
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 14);
            this.ClientSize = new System.Drawing.Size(287, 225);
            this.Controls.Add(this.cboLoainv);
            this.Controls.Add(this.gpBPTTT);
            this.Controls.Add(this.cboLoaipt);
            this.Controls.Add(this.label4);
            this.Controls.Add(this.label3);
            this.Controls.Add(this.groupKx);
            this.Controls.Add(this.label5);
            this.Controls.Add(this.txtthang);
            this.Controls.Add(this.txtnam);
            this.Controls.Add(this.buttonkethuc);
            this.Controls.Add(this.buttonbcao);
            this.Controls.Add(this.combodvi);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.label1);
            this.Controls.Add(this.groupBox2);
            this.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedToolWindow;
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.MaximizeBox = false;
            this.Name = "frmbc_Pcap_pttt";
            this.ShowIcon = false;
            this.ShowInTaskbar = false;
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Phụ cấp mổ";
            this.Load += new System.EventHandler(this.frmbcttluong_Load);
            this.groupKx.ResumeLayout(false);
            this.groupKx.PerformLayout();
            this.gpBPTTT.ResumeLayout(false);
            this.gpBPTTT.PerformLayout();
            this.ResumeLayout(false);
            this.PerformLayout();

		}
		#endregion

		private void buttonkethuc_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}
		
		private void frmbcttluong_Load(object sender, System.EventArgs e)
		{
            thang = txtthang.Text = DateTime.Now.Month.ToString().PadLeft(2, '0');
            nam = txtnam.Text = DateTime.Now.Year.ToString();
			tn=thang+nam.Substring(2,2);
			Load_bophan();	
            loadhopdong();
            loadloaipttt();
            loadchucdanh(); 
  		}
        private void loadhopdong()
        {
            dsHd = mDB.get_nhombc();
            cboLoainv.DataSource = dsHd.Tables[0];
            cboLoainv.DisplayMember = "tennhom";
            cboLoainv.ValueMember = "manhom";
            cboLoainv.SelectedIndex = -1;
        }
        private void loadchucdanh()
        {
            string sql = "";
            sql = "select '' as manv,'' as hoten,'' as  madv,'' as tenbp,0 as  c ,0 as c2 ,0 as p,";
            sql += " 0 as p2 ,0 as k,0 as  t_c ,0 as t_c2 ,0 as t_p,";
            sql += " 0 as t_p2 ,0 as t_k,";
            sql += " '' as loai,'' as pttt,'' as tenloai,0 as tongtien   from " + mDB.user + ".dmcd ";
            dt_rp = mDB.get_data(sql);
        }

       

        private void loadloaipttt()
        {
            DataRow r;
            try
            {
                ds_loaipt.ReadXml("..\\xml\\h_loaipttt.xml");

            }
            catch
            {
                ds_loaipt.Tables.Add("loaipttt");
                ds_loaipt.Tables[0].Columns.Add("loai", typeof(string));
                ds_loaipt.Tables[0].Columns.Add("tenloai", typeof(string));

                r = ds_loaipt.Tables[0].NewRow();
                r["loai"] = "db";
                r["tenloai"] = "Đặc biệt";
                ds_loaipt.Tables[0].Rows.Add(r);

                r = ds_loaipt.Tables[0].NewRow();
                r["loai"] = "l1";
                r["tenloai"] = "Loại 1";
                ds_loaipt.Tables[0].Rows.Add(r);
                //
                r = ds_loaipt.Tables[0].NewRow();
                r["loai"] = "l2";
                r["tenloai"] = "Loại 2";
                ds_loaipt.Tables[0].Rows.Add(r);
                //
                r = ds_loaipt.Tables[0].NewRow();
                r["loai"] = "l3";
                r["tenloai"] = "Loại 3";
                ds_loaipt.Tables[0].Rows.Add(r);
                //
                ds_loaipt.WriteXml("..\\xml\\h_loaipttt.xml", XmlWriteMode.WriteSchema);

            }
            cboLoaipt.ValueMember = "loai";
            cboLoaipt.DisplayMember = "tenloai";
            cboLoaipt.DataSource = ds_loaipt.Tables[0];
            cboLoaipt.SelectedIndex = -1;
        }

		private void Load_bophan()
		{
			try
			{
				DataSet ds=new	DataSet();
				string sql="select madv,tenbp from "+mDB.user+".bophan";
				ds=mDB.get_data(sql);
				combodvi.DataSource=ds.Tables[0];
				combodvi.DisplayMember="tenbp";
				combodvi.ValueMember="madv";
                combodvi.SelectedIndex = -1;
			}
			catch{}				
		}
        
		private decimal tinhtong()
		{	
			decimal s=0;
            string sql;
			try
			{
				sql=" select sum(lg_caba+lg_cn+lg_letet) as tong";
				sql+=" from bcc"+tn+" ";
				if(combodvi.SelectedIndex >=0)
					sql+=" where  bcc"+tn+".madv='"+combodvi.SelectedValue.ToString()+"'";
				DataSet dst=new DataSet();
				try	
				{
					dst=mDB.get_data(sql);
					s=decimal.Parse(dst.Tables[0].Rows[0]["tong"].ToString());
				}
				catch(Exception ex)
				{
					MessageBox.Show(ex.ToString());
				}				
				if ( s > 0 )	
					s=decimal.Parse(s.ToString("############"));
			}
			catch{}
			return s;

		}    	
		private void xem_Click(object sender, System.EventArgs e)
		{
            thang=txtthang.Text.Trim().PadLeft(2,'0');
			nam=txtnam.Text;			
			if(!mDB.kt_thangnam(thang,nam))	 return;
			tn=thang+nam.Substring(2,2);

			if(!mDB.bMmyy(tn))//  || !mDB.kt_heso(thang,nam)
			{
				MessageBox.Show(lan.Change_language_MessageText("Dữ liệu chưa tạo !"),lan.Change_language_MessageText("Thông báo"));
				return;
            }
            DataSet ds=tonghopdata();
          
            msg = lan.Change_language_MessageText("Ngày ")+DateTime.Now.Day.ToString().PadLeft(2,'0');
            msg += lan.Change_language_MessageText(" tháng ") + DateTime.Now.Month.ToString().PadLeft(2, '0');
            msg += lan.Change_language_MessageText(" năm ") + DateTime.Now.Year.ToString();
            if (ds.Tables[0].Rows.Count > 0)
            {
                rpt = new frmReport(mDB, ds, "h_thanhtoanpcpt.rpt",lan.Change_language_MessageText( "THANH TOÁN PHỤ CẤP PHẨU THUẬT, THỦ THUẬT, CHỐNG CHOÁNG THÁNG ") +" "+ thang +" "+ lan.Change_language_MessageText(" NĂM ") +" "+ nam, msg);
                rpt.ShowDialog(this);
            }
            else
            {
                MessageBox.Show(lan.Change_language_MessageText("Không có dữ liệu!"), AccessData.Msg, MessageBoxButtons.OK, MessageBoxIcon.Warning);
               
            }
           
        }

        DataSet dsth;

        #region Lấy dữ liệu chấm công phụ cấp phẫu thuật

        public DataSet getdataphauthuat()
        {
            string sql = "";
            sql = " (select 'Chính' as capbacmo,bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'ptv' as key,db_c as db,0::numeric as t_db";
            sql += " ,l1_c as l1,0::numeric as t_l1,l2_c as l2,0::numeric as t_l2,l3_c as l3,0::numeric as t_l3 ";
            sql += " from " + mDB.user + ".llnv l join " + mDB.user + ".th_chamcong_" + tn + " thcc on thcc.manv=l.manv join " + mDB.user + ".bophan bp on l.madv=bp.madv";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += " where (db_c+l1_c+l2_c+l3_c)>0 ";            
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ")";
            sql += ")";
            sql += " union ";          
            sql += "(select 'BS Gây mê' as capbacmo,bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'bsgayme' as key,db_bs as db";
            sql += " ,0::numeric as t_db,l1_bs as l1,0::numeric as t_l1,l2_bs as l2,0::numeric as t_l2,l3_bs as l3,0::numeric as t_l3 ";
            sql += " from " + mDB.user + ".llnv l join " + mDB.user + ".th_chamcong_"+tn+" thcc on thcc.manv=l.manv join " + mDB.user + ".bophan bp on l.madv=bp.madv ";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += " where (db_bs+l1_bs+l2_bs+l3_bs)>0  ";           
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ") ";
            sql += ")";

            sql += " union ";
            sql += "(select 'KTV Gây mê' as capbacmo,bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'ktvgayme' as key,db_k as db";
            sql += " ,0::numeric as t_db,l1_k as l1,0::numeric as t_l1,l2_k as l2,0 as t_l2,l3_k as l3,0 as t_l3 ";
            sql += " from " + mDB.user + ".llnv l join " + mDB.user + ".th_chamcong_" + tn + " thcc on thcc.manv=l.manv join " + mDB.user + ".bophan bp on bp.madv=l.madv  ";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += "where (db_k+l1_k+l2_k+l3_k)>0 ";
          
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ") ";
            sql += ")";

            sql += " union ";
         
            sql += "(select 'Phụ 1' as capbacmo,bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'phu1' as key,db_p1 as db";
            sql += " ,0::numeric as t_db,l1_p1 as l1,0::numeric as t_l1,l2_p1 as l2,0::numeric as t_l2,l3_p1 as l3";
            sql += " ,0::numeric as t_l3 from " + mDB.user + ".llnv l join " + mDB.user + ".th_chamcong_"+tn+" thcc on thcc.manv=l.manv";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += " join " + mDB.user + ".bophan bp on l.madv=bp.madv where (db_p1+l1_p1+l2_p1+l3_p1)>0 ";
           
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ") ";
            sql += ")";
            
            sql += " union ";         
            sql += "(select 'Phụ 2' as capbacmo,bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'phu2' as key,db_p2 as db";
            sql += " ,0::numeric as t_db,l1_p2 as l1,0::numeric as t_l1,l2_p2 as l2,0::numeric as t_l2,l3_p2 as l3,0::numeric as t_l3 ";
            sql += " from " + mDB.user + ".llnv l join " + mDB.user + ".th_chamcong_"+tn+" thcc on thcc.manv=l.manv  join " + mDB.user + ".bophan bp on bp.madv=l.madv";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += " where (db_p2+l1_p2+l2_p2+l3_p2)>0 ";           
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ") ";
            sql += ")";

            sql += " union ";
            sql += "(select 'sGiúp Việc' as capbacmo,bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'giupviec' as key,db_gv as db,0::numeric as t_db";
            sql += ",l1_gv as l1,0::numeric as t_l1,l2_gv as l2,0::numeric as t_l2,l3_gv as l3,0::numeric as t_l3 ";
            sql += " from " + mDB.user + ".llnv l join " + mDB.user + ".th_chamcong_"+tn+" thcc on thcc.manv=l.manv  join " + mDB.user + ".bophan bp on bp.madv=l.madv ";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += " where (db_gv+l1_gv+l2_gv+l3_gv)>0";
          
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ") ";
            sql += ")";

            sql += " union ";
            sql += " (select 'sDụng cụ' as capbacmo,bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'dungcu' as key,db_dc as db,0::numeric as t_db,l1_dc as l1";
            sql += " ,0::numeric as t_l1,l2_dc as l2,0::numeric as t_l2,l3_dc as l3,0::numeric as t_l3 from " + mDB.user + ".llnv l";
            sql += " join " + mDB.user + ".th_chamcong_"+tn+" thcc on thcc.manv=l.manv join " + mDB.user + ".bophan bp on bp.madv=l.madv";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += " where (db_dc+l1_dc+l2_dc+l3_dc)>0";
          
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ") ";
            sql += ")";

            try
            {
                return mDB.get_data(sql);
            }
            catch
            {
                return null;
            }
        }

        #endregion

        #region Lấy dữ liệu chấm công phụ cấp thủ thuật
        public DataSet getdatathuthuat()
        {
            string sql = "(select bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'Chính' as capbacmo,'ptv' as key,";
            sql += "tdb_c as db,0::numeric as t_db,tl1_c as l1,0::numeric as t_l1,tl2_c as l2,0::numeric as t_l2,tl3_c as l3,0::numeric as t_l3 ";
            sql += "from " + mDB.user + ".llnv l ";
            sql += "join " + mDB.user + ".th_chamcong_" + tn + " thcc on thcc.manv=l.manv ";            
            sql += "join " + mDB.user + ".bophan bp on bp.madv=l.madv ";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += "where (tdb_c+tl1_c+tl2_c+tl3_c)>0 ";
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ")";
            sql += ")";
            sql += " union ";

            sql += "(select bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'BS Gây mê' as capbacmo,'bsgayme' as key,";
            sql += "tdb_bs as db,0::numeric as t_db,tl1_bs as l1,0::numeric as t_l1,tl2_bs as l2,0::numeric as t_l2,tl3_bs as l3,0::numeric as t_l3 ";
            sql += "from " + mDB.user + ".llnv l ";
            sql += "join " + mDB.user + ".th_chamcong_" + tn + " thcc on thcc.manv=l.manv ";            
            sql += "join " + mDB.user + ".bophan bp on bp.madv=l.madv ";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += "where (tdb_bs+tl1_bs+tl2_bs+tl3_bs)>0  ";
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ") ";
            sql += ")";

            sql += " union ";

            sql += "(select bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'KTV Gây mê' as capbacmo,'ktvgayme' as key,";
            sql += " tdb_k as db,0::numeric as t_db,tl1_k as l1,0::numeric as t_l1,tl2_k as l2,0 as t_l2,tl3_k as l3,0 as t_l3 ";
            sql += " from " + mDB.user + ".llnv l";
            sql += " join " + mDB.user + ".th_chamcong_" + tn + " thcc on thcc.manv=l.manv ";
            sql += " join " + mDB.user + ".bophan bp on bp.madv=l.madv ";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += "where (tdb_k+tl1_k+tl2_k+tl3_k)>0 "; 
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ") ";
            sql += ")";

            sql += " union ";

            sql += "(select bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'Phụ 1' as capbacmo,'phu1' as key,";
            sql += "tdb_p1 as db,0::numeric as t_db,tl1_p1 as l1,0::numeric as t_l1,tl2_p1 as l2,0::numeric as t_l2,tl3_p1 as l3,0::numeric as t_l3 ";
            sql += "from " + mDB.user + ".llnv l ";
            sql += "join " + mDB.user + ".th_chamcong_" + tn + " thcc on thcc.manv=l.manv ";           
            sql += "join " + mDB.user + ".bophan bp on bp.madv=l.madv ";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += "where (tdb_p1+tl1_p1+tl2_p1+tl3_p1)>0 ";
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ") ";
            sql += ")";

            sql += " union ";

            sql += "(select bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'Phụ 2' as capbacmo,'phu2' as key,";
            sql += "tdb_p2 as db,0::numeric as t_db,tl1_p2 as l1,0::numeric as t_l1,tl2_p2 as l2,0::numeric as t_l2,tl3_p2 as l3,0::numeric as t_l3 ";
            sql += "from " + mDB.user + ".llnv l ";
            sql += "join " + mDB.user + ".th_chamcong_" + tn + " thcc on thcc.manv=l.manv ";
            sql += "join " + mDB.user + ".bophan bp on bp.madv=l.madv ";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += "where (tdb_p2+tl1_p2+tl2_p2+tl3_p2)>0 ";
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ") ";
            sql += ")";
            sql += " union ";

            sql += "(select bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'sGiúp Việc' as capbacmo,'giupviec' as key,";
            sql += "tdb_gv as db,0::numeric as t_db,tl1_gv as l1,0::numeric as t_l1,tl2_gv as l2,0::numeric as t_l2,tl3_gv as l3,0::numeric as t_l3 ";
            sql += "from " + mDB.user + ".llnv l ";
            sql += "join " + mDB.user + ".th_chamcong_" + tn + " thcc on thcc.manv=l.manv ";
            sql += "join " + mDB.user + ".bophan bp on bp.madv=l.madv ";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += "where (tdb_gv+tl1_gv+tl2_gv+tl3_gv)>0 ";
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ") ";
            sql += ")";

            sql += " union ";

            sql += "(select bp.tenbp,l.manv,l.ho||' '||l.ten as hoten,'sDụng cụ' as capbacmo,'dungcu' as key,";
            sql += "tdb_dc as db,0::numeric as t_db,tl1_dc as l1,0::numeric as t_l1,tl2_dc as l2,0::numeric as t_l2,tl3_dc as l3,0::numeric as t_l3 ";
            sql += "from " + mDB.user + ".llnv l ";
            sql += "join " + mDB.user+ ".th_chamcong_" + tn + " thcc on thcc.manv=l.manv ";
            sql += "join " + mDB.user + ".bophan bp on bp.madv=l.madv ";
            sql += " inner join " + mDB.user + ".hopdong hd on l.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += "where (tdb_dc+tl1_dc+tl2_dc+tl3_dc)>0 ";
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "' ";
            if (cboLoainv.SelectedIndex != -1)
                sql += " and mh.nhombc in (" + cboLoainv.SelectedValue.ToString() + ") ";
            sql += ")";
            try
            {
                return mDB.get_data(sql);
            }
            catch
            {
                return null;
            }
        }
        #endregion


        public DataSet gethsotrocap_pt()
        {
            string sql = "select sum(case when loai='db' then ptv::decimal end) as db,sum(case when loai='l1' then ptv::decimal end) as l1,";
            sql+="sum(case when loai='l2' then ptv::decimal end) as l2,sum(case when loai='l3' and pttt<>'T' then ptv::decimal end) as l3";
            sql+=",'ptv' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='P'";

            sql+=" union ";

            sql+="select sum(case when loai='db' then bsgayme::decimal end) as db,sum(case when loai='l1' then bsgayme::decimal end) as l1,";
            sql+="sum(case when loai='l2' then bsgayme::decimal end) as l2,sum(case when loai='l3' and pttt<>'T' then bsgayme::decimal end) as l3";
            sql+=",'bsgayme' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='P'";

            sql+=" union ";

            sql+="select sum(case when loai='db' then ktvgayme::decimal end) as db,sum(case when loai='l1' then ktvgayme::decimal end) as l1,";
            sql+="sum(case when loai='l2' then ktvgayme::decimal end) as l2,sum(case when loai='l3' and pttt<>'T' then ktvgayme::decimal end) as l3,";
            sql+=" 'ktvgayme' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='P'";

            sql+=" union ";

            sql+="select sum(case when loai='db' then phu1::decimal end) as db,sum(case when loai='l1' then phu1::decimal end) as l1,";
            sql+="sum(case when loai='l2' then phu1::decimal end) as l2,sum(case when loai='l3' and pttt<>'T' then phu1::decimal end) as l3,";
            sql+=" 'phu1' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='P'";

            sql+=" union ";

            sql+="select sum(case when loai='db' then phu2::decimal end) as db,sum(case when loai='l1' then phu2::decimal end) as l1,";
            sql+="sum(case when loai='l2' then phu2::decimal end) as l2,sum(case when loai='l3' and pttt<>'T' then phu2::decimal end) as l3,";
            sql+=" 'phu2' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='P'";

            sql += " union ";

            sql += "select sum(case when loai='db' then giupviec::decimal end) as db,sum(case when loai='l1' then giupviec::decimal end) as l1,";
            sql += "sum(case when loai='l2' then giupviec::decimal end) as l2,sum(case when loai='l3' and pttt<>'T' then giupviec::decimal end) as l3,";
            sql += " 'giupviec' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='P'";

            sql += " union ";

            sql += "select sum(case when loai='db' then dungcu::decimal end) as db,sum(case when loai='l1' then dungcu::decimal end) as l1,";
            sql += "sum(case when loai='l2' then dungcu::decimal end) as l2,sum(case when loai='l3' and pttt<>'T' then dungcu::decimal end) as l3,";
            sql += " 'dungcu' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='P'";
            try
            {
                return mDB.get_data(sql);
            }
            catch
            { return null; }

        }
        public DataSet gethsotrocap_tt()
        {
            string sql = "select sum(case when loai='db' then ptv::decimal end) as db,sum(case when loai='l1' then ptv::decimal end) as l1,";
            sql += "sum(case when loai='l2' then ptv::decimal end) as l2,sum(case when loai='l3' then ptv::decimal end) as l3";
            sql += ",'ptv' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='T'";

            sql += " union ";

            sql += "select sum(case when loai='db' then bsgayme::decimal end) as db,sum(case when loai='l1' then bsgayme::decimal end) as l1,";
            sql += "sum(case when loai='l2' then bsgayme::decimal end) as l2,sum(case when loai='l3' then bsgayme::decimal end) as l3";
            sql += ",'bsgayme' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='T'";

            sql += " union ";

            sql += "select sum(case when loai='db' then ktvgayme::decimal end) as db,sum(case when loai='l1' then ktvgayme::decimal end) as l1,";
            sql += "sum(case when loai='l2' then ktvgayme::decimal end) as l2,sum(case when loai='l3' then ktvgayme::decimal end) as l3,";
            sql += " 'ktvgayme' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='T'";

            sql += " union ";

            sql += "select sum(case when loai='db' then phu1::decimal end) as db,sum(case when loai='l1' then phu1::decimal end) as l1,";
            sql += "sum(case when loai='l2' then phu1::decimal end) as l2,sum(case when loai='l3' then phu1::decimal end) as l3,";
            sql += " 'phu1' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='T'";

            sql += " union ";

            sql += "select sum(case when loai='db' then phu2::decimal end) as db,sum(case when loai='l1' then phu2::decimal end) as l1,";
            sql += "sum(case when loai='l2' then phu2::decimal end) as l2,sum(case when loai='l3' then phu2::decimal end) as l3,";
            sql += " 'phu2' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='T'";

            sql += " union ";

            sql += "select sum(case when loai='db' then giupviec::decimal end) as db,sum(case when loai='l1' then giupviec::decimal end) as l1,";
            sql += "sum(case when loai='l2' then giupviec::decimal end) as l2,sum(case when loai='l3' then giupviec::decimal end) as l3,";
            sql += " 'giupviec' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='T'";

            sql += " union ";

            sql += "select sum(case when loai='db' then dungcu::decimal end) as db,sum(case when loai='l1' then dungcu::decimal end) as l1,";
            sql += "sum(case when loai='l2' then dungcu::decimal end) as l2,sum(case when loai='l3' then dungcu::decimal end) as l3,";
            sql += " 'dungcu' as macc ";
            sql += "from " + mDB.user + ".boiduong_pttt where mmyy='" + tn + "' and pttt='T'";
            try
            {
                return mDB.get_data(sql);
            }
            catch
            { return null; }

        }
        public DataSet tonghopdata()
        {
            _dtam = NewTable();
            decimal db, t_db, l1, t_l1, l2, t_l2, l3, t_l3,tien;
            DataSet dshs =new DataSet(); 
            if (rdPT.Checked)
            {
                dsth = getdataphauthuat();
                dshs = gethsotrocap_pt();
                if (dsth.Tables[0].Rows.Count > 0)
                    foreach (DataRow dr in dsth.Tables[0].Rows)
                    {
                        db = 0; t_db = 0; l1 = 0; t_l1 = 0; l2 = 0; t_l2 = 0; l3 = 0; t_l3 = 0; tien = 0;
                        DataRow[] dr1 = dshs.Tables[0].Select("macc='" + dr["key"].ToString() + "'");
                        if (dr1.Length > 0)
                        {
                            db = dr["db"].ToString().Trim() != "" ? Convert.ToDecimal(dr["db"].ToString()) : 0;
                            if (db > 0)
                            {
                                t_db = dr1[0]["db"].ToString().Trim() != "" ? Convert.ToDecimal(dr1[0]["db"].ToString()) : 0;
                                dr["t_db"] = t_db;
                            }
                            l1 = dr["l1"].ToString().Trim() != "" ? Convert.ToDecimal(dr["l1"].ToString()) : 0;
                            if (l1 > 0)
                            {
                                t_l1 = dr1[0]["l1"].ToString().Trim() != "" ? Convert.ToDecimal(dr1[0]["l1"].ToString()) : 0;
                                dr["t_l1"] = t_l1;
                            }
                            l2 = dr["l2"].ToString().Trim() != "" ? Convert.ToDecimal(dr["l2"].ToString()) : 0;
                            if (l2 > 0)
                            {
                                t_l2 = dr1[0]["l2"].ToString().Trim() != "" ? Convert.ToDecimal(dr1[0]["l2"].ToString()) : 0;
                                dr["t_l2"] = t_l2;
                            }
                            l3 = dr["l3"].ToString().Trim() != "" ? Convert.ToDecimal(dr["l3"].ToString()) : 0;
                            if (l3 > 0)
                            {
                                t_l3 = dr1[0]["l3"].ToString().Trim() != "" ? Convert.ToDecimal(dr1[0]["l3"].ToString()) : 0;
                                dr["t_l3"] = t_l3;
                            }

                            if (dr["key"].ToString() == "giupviec")
                                dr["capbacmo"] = "Giúp việc";
                            if (dr["key"].ToString() == "dungcu")
                                dr["capbacmo"] = "Dụng cụ";

                            DataRow[] r = _dtam.Select("manv='" + dr["manv"].ToString().Trim() + "'");
                            if (r.Length > 0)
                            {
                                tien = r[0]["tongtien"].ToString().Trim() != "" ? Convert.ToDecimal(r[0]["tongtien"].ToString()) : 0;
                                r[0]["tongtien"] = (tien + db * t_db + l1 * t_l1 + l2 * t_l2 + l3 * t_l3);
                            }
                            else
                            {
                                _dtam.Rows.Add(dr["manv"].ToString().Trim(), (db * t_db + l1 * t_l1 + l2 * t_l2 + l3 * t_l3));
                            }
                        }
                    }
            }
            else
            {
                dsth = getdatathuthuat();
                dshs = gethsotrocap_tt();
                if (dsth.Tables[0].Rows.Count > 0)
                    foreach (DataRow dr in dsth.Tables[0].Rows)
                    {
                        db = 0; t_db = 0; l1 = 0; t_l1 = 0; l2 = 0; t_l2 = 0; l3 = 0; t_l3 = 0; tien = 0;
                        DataRow[] dr1 = dshs.Tables[0].Select("macc='" + dr["key"].ToString() + "'");
                        if (dr1.Length > 0)
                        {
                            db = dr["db"].ToString().Trim() != "" ? Convert.ToDecimal(dr["db"].ToString()) : 0;
                            if (db > 0)
                            {
                                t_db = dr1[0]["db"].ToString().Trim() != "" ? Convert.ToDecimal(dr1[0]["db"].ToString()) : 0;
                                dr["t_db"] = t_db;
                            }
                            l1 = dr["l1"].ToString().Trim() != "" ? Convert.ToDecimal(dr["l1"].ToString()) : 0;
                            if (l1 > 0)
                            {
                                t_l1 = dr1[0]["l1"].ToString().Trim() != "" ? Convert.ToDecimal(dr1[0]["l1"].ToString()) : 0;
                                dr["t_l1"] = t_l1;
                            }
                            l2 = dr["l2"].ToString().Trim() != "" ? Convert.ToDecimal(dr["l2"].ToString()) : 0;
                            if (l2 > 0)
                            {
                                t_l2 = dr1[0]["l2"].ToString().Trim() != "" ? Convert.ToDecimal(dr1[0]["l2"].ToString()) : 0;
                                dr["t_l2"] = t_l2;
                            }
                            l3 = dr["l3"].ToString().Trim() != "" ? Convert.ToDecimal(dr["l3"].ToString()) : 0;
                            if (l3 > 0)
                            {
                                t_l3 = dr1[0]["l3"].ToString().Trim() != "" ? Convert.ToDecimal(dr1[0]["l3"].ToString()) : 0;
                                dr["t_l3"] = t_l3;
                            }

                            if (dr["key"].ToString() == "giupviec")
                                dr["capbacmo"] = "Giúp việc";
                            if (dr["key"].ToString() == "dungcu")
                                dr["capbacmo"] = "Dụng cụ";

                            DataRow[] r = _dtam.Select("manv='" + dr["manv"].ToString().Trim() + "'");
                            if (r.Length > 0)
                            {
                                tien = r[0]["tongtien"].ToString().Trim() != "" ? Convert.ToDecimal(r[0]["tongtien"].ToString()) : 0;
                                r[0]["tongtien"] = (tien + db * t_db + l1 * t_l1 + l2 * t_l2 + l3 * t_l3);
                            }
                            else
                            {
                                _dtam.Rows.Add(dr["manv"].ToString().Trim(), (db * t_db + l1 * t_l1 + l2 * t_l2 + l3 * t_l3));
                            }
                        }
                    }
            }
            dsth.WriteXml("..\\xml\\h_thanhtoanpcpt.xml", XmlWriteMode.WriteSchema);
            return dsth;
        }
        #region Không sử dụng đến
        private void Tonghop(string m_mmyy,string m_madv,string ploai,string p_pttt )
        {
            string sql = "";
            DataSet ds_tmp = new DataSet();
            string f1 = "";
            string[] arr;
            decimal gia = 0;
            DataRow r1, r2;
            DataRow[] dr; 
            switch (p_pttt)
            {
                case "P":
                    sql = " select l1_c,l1_c2,l1_p,l1_p2,l1_k ,";
                    sql += "l2_c ,l2_c2, l2_p , l2_p2 , l2_k , l3_c , l3_c2 , l3_p,";
                    sql += " l3_p2 , l3_k , db_c , db_c2 , db_p , db_p2 , db_k, ";
                    sql += " trim(b.ho)||' '||trim(b.ten) as hoten,c.ten as chucdanh,a.manv,a.madv,a.mmyy,d.tenbp ";
                    sql += "  from " + mDB.user + ".pc a," + mDB.user + ".nv b,";
                    sql += " " + mDB.user + ".dmcd c,"+mDB.user + ".bophan d";
                    sql += " where a.manv=b.manv and a.mmyy=b.mmyy and b.macd=c.ma and a.madv=d.madv ";
                    sql += " and a.mmyy='" + m_mmyy + "'";
                    if (m_madv!="")
                      sql+=" and a.madv='" + m_madv + "'";
                    
                    ds_tmp = mDB.get_data(sql);
                    
                    foreach (DataRow r in ds_tmp.Tables[0].Rows)
                    {
                        for (int i = 0; i <= 19; i++)
                        {
                            f1=ds_tmp.Tables[0].Columns[i].ColumnName.ToString();
                            if (r[f1].ToString() != "0")
                            {
                                arr = f1.Split('_');
                                gia = get_gia(p_pttt, arr[0], arr[1]);
                                r1 = mDB.getrowbyid(dt_rp.Tables[0], "manv='" + r["manv"].ToString() + "' and loai='" + arr[0] + "' and pttt='" + p_pttt + "'");
                                if (r1 == null)
                                {
                                    r2 = dt_rp.Tables[0].NewRow();
                                    r2["manv"] = r["manv"].ToString();
                                    r2["hoten"] = r["hoten"].ToString();
                                    r2["loai"] = arr[0];
                                    r2["pttt"] = p_pttt;
                                    r2["madv"] = r["madv"].ToString();
                                    r2["tenbp"] = r["tenbp"].ToString();
                                    r2["tenloai"] = mDB.getrowbyid(ds_loaipt.Tables[0], "loai='" + arr[0] + "'")["tenloai"].ToString();
                                    try
                                    {
                                        r2["t_" + arr[1]] = decimal.Parse(r[f1].ToString()) * gia;
                                        r2[arr[1]] = decimal.Parse(r[f1].ToString());
                                        r2["tongtien"] = decimal.Parse(r[f1].ToString()) * gia;
                                    }
                                    catch { }
                                    dt_rp.Tables[0].Rows.Add(r2);
                                }
                                else
                                {
                                    dr = dt_rp.Tables[0].Select("manv='" + r["manv"].ToString() + "' and loai='" + arr[0] + "' and pttt='" + p_pttt + "'");
                                    dr[0]["t_" + arr[1]] = decimal.Parse(r[f1].ToString()) * gia;
                                    dr[0][arr[1]] = decimal.Parse(r[f1].ToString()) ;
                                    dr[0]["tongtien"] = decimal.Parse(dr[0]["tongtien"].ToString()) + decimal.Parse(r[f1].ToString()) * gia;
                                }
                            }
                        }
                    }
                    break;
                case "T":
                    //l1_c,l1_c2,l1_p,l1_p2,l1_k
                    sql = " select tt1 as l3_c,tt2 l3_c2,tt3 as l3_p,tt42 as l3_p2,tt43 as l3_k,";
                    sql += " trim(b.ho)||' '||trim(b.ten) as hoten,c.ten as chucdanh,a.manv,a.madv,a.mmyy,d.tenbp ";
                    sql += "  from " + mDB.user + ".pc a," + mDB.user + ".nv b,";
                    sql += " " + mDB.user + ".dmcd c," + mDB.user + ".bophan d";
                    sql += " where a.manv=b.manv and a.mmyy=b.mmyy and b.macd=c.ma(+) and a.madv=d.madv ";
                    sql += " and a.mmyy='" + m_mmyy + "'";
                    if (m_madv != "")
                        sql += " and a.madv='" + m_madv + "'";

                    ds_tmp = mDB.get_data(sql);
                    foreach (DataRow r in ds_tmp.Tables[0].Rows)
                    {
                        for (int i = 0; i <= 4; i++)
                        {
                            f1 = ds_tmp.Tables[0].Columns[i].ColumnName.ToString();
                            if (r[f1].ToString() != "0")
                            {
                                arr = f1.Split('_');
                                gia = get_gia(p_pttt, arr[0], arr[1]);
                                r1 = mDB.getrowbyid(dt_rp.Tables[0], "manv='" + r["manv"].ToString() + "' and loai='" + arr[0] + "' and pttt='" + p_pttt + "'");
                                if (r1 == null)
                                {
                                    r2 = dt_rp.Tables[0].NewRow();
                                    r2["manv"] = r["manv"].ToString();
                                    r2["hoten"] = r["hoten"].ToString();
                                    r2["loai"] = arr[0];
                                    r2["pttt"] = p_pttt;
                                    r2["madv"] = r["madv"].ToString();
                                    r2["tenbp"] = r["tenbp"].ToString();
                                    r2["tenloai"] = mDB.getrowbyid(ds_loaipt.Tables[0], "loai='" + arr[0] + "'")["tenloai"].ToString();
                                    r2["t_" + arr[1]] = decimal.Parse(r[f1].ToString()) * gia;
                                    r2[arr[1]] = decimal.Parse(r[f1].ToString()); 
                                    r2["tongtien"] = decimal.Parse(r[f1].ToString()) * gia;
                                    dt_rp.Tables[0].Rows.Add(r2);
                                }
                                else
                                {
                                    dr = dt_rp.Tables[0].Select("manv='" + r["manv"].ToString() + "' and loai='" + arr[0] + "' and pttt='" + p_pttt + "'");
                                    dr[0]["t_" + arr[1]] = decimal.Parse(r[f1].ToString()) * gia;
                                    dr[0][arr[1]] = decimal.Parse(r[f1].ToString());
                                    dr[0]["tongtien"] = decimal.Parse(dr[0]["tongtien"].ToString()) + decimal.Parse(r[f1].ToString()) * gia;
                                }
                            }
                        }
                    }
                    break;
        }
            
        }
        decimal get_gia(string pttt, string loai,string f1)
        {
            decimal gttv = 0;
            string m_dk = "";
            if (pttt!="")
                m_dk= "pttt='" + pttt + "'";// and loai='" + loai + "'"
            if (loai != "")
            {
                if (m_dk != "")
                    m_dk += " and loai='" + loai + "'";
                else
                    m_dk= " loai='" + loai + "'";
            }
            DataRow r = mDB.getrowbyid(dt_dmgia, m_dk);
            if (r != null)
                gttv =decimal.Parse(  r[f1].ToString());
            return gttv;
        }
        
        private void button1_Click(object sender, System.EventArgs e)
		{
			thang=txtthang.Text.Trim().PadLeft(2,'0');
			nam=txtnam.Text;			
			if(!mDB.kt_thangnam(thang,nam))	 return;
			tn=thang+nam.Substring(2,2);
			if(!mDB.bMmyy(tn)) // ||  !mDB.kt_heso(thang,nam)
			{
				MessageBox.Show(lan.Change_language_MessageText("Số liệu chưa tạo !"),lan.Change_language_MessageText("Thông báo"));
				return;
			}
			//print(1);
        }
        #endregion

        #region Các hàm ràng buộc dữ liệu nhập và sendTab
        private void txtnam_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab},{F4}");
		}

		private void cboloaihd_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab},{F4}");
		}	

		private void combodvi_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab}");
		}	

		private void buttonkethuc_Click_1(object sender, System.EventArgs e)
		{
			this.Close();
		}

		private void txtthang_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab}");
		}

        private void optMH_CheckedChanged(object sender, EventArgs e)
        {
            if (optMH.Checked)
                groupKx.Tag = "0";
        }

        private void rdIn_CheckedChanged(object sender, EventArgs e)
        {
            if (rdIn.Checked)
                groupKx.Tag = "1";
        }

        private void rdExcel_CheckedChanged(object sender, EventArgs e)
        {
            if (rdExcel.Checked)
                groupKx.Tag = "2";
        }

        private void rdPT_CheckedChanged(object sender, EventArgs e)
        {
            gpBPTTT.Tag = "P";
        }

        private void rdTT_CheckedChanged(object sender, EventArgs e)
        {
            gpBPTTT.Tag = "T";
        }

        private void label4_Click(object sender, EventArgs e)
        {

        }

        private void gpBPTTT_Enter(object sender, EventArgs e)
        {

        }

        private void cboLoaipt_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void txtthang_KeyPress(object sender, KeyPressEventArgs e)
        {
            if ((e.KeyChar < '0' || e.KeyChar > '9') && e.KeyChar != '\b')
                e.KeyChar = '\0';
        }
        #endregion

        private DataTable NewTable()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("manv",typeof(string));
            dt.Columns.Add("tongtien",typeof(Decimal));
            return dt;
        }
    }
}
